
import java.io.File;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Random;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.w3c.dom.Document;
import org.w3c.dom.Node;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 *
 * @author Patrick
 */
public class CrombManager {

    private final static String SQL_GET_CROMBS = "SELECT id,author,message,entrytime as datetime, longitude,latitude,likecounter,userid FROM cromb where longitude between ? and ? AND latitude between ? and ? order by entrytime desc LIMIT 5000";
    private final static String SQL_GET_COUNT = "SELECT count(*) as counter FROM cromb where longitude between ? and ? and latitude between ? and ?";
    private final static String SQL_PUT_CROMB = "INSERT INTO cromb (author, message, longitude,latitude,userId) VALUES (?,?,?,?,?)";
    private final static String SQL_GET_CLOSEST = "SELECT longitude,latitude from (SELECT longitude,latitude,glength(linestring(point(longitude,latitude),point(?,?))) as distance FROM cromb where longitude between ? and ? and latitude between ? and ? order by distance) as q where distance > 0.002 limit 1";
    private final static String SQL_PUT_TAGS = "INSERT INTO hashtags (tag, longitude,latitude) VALUES (?,?,?)";
    private final static String SQL_GET_TAG_LIST = "SELECT tag,longitude,latitude from hashtags where longitude between ? and ? and latitude between ? and ? order by tag";
    private final static String SQL_GET_TAG_LOCATIONS = "SELECT tag,longitude,latitude from hashtags where longitude between ? and ? and latitude between ? and ? AND tag=? order by tag";

    public static int getCrombsCount(Connection con, double lon, double lat) throws SQLException {
        int count = 0;
        double minLon = lon - 0.1;
        double minLat = lat - 0.1;
        double maxLon = lon + 0.1;
        double maxLat = lat + 0.1;

        PreparedStatement stat = con.prepareStatement(SQL_GET_COUNT);
        stat.setDouble(1, minLon);
        stat.setDouble(2, maxLon);
        stat.setDouble(3, minLat);
        stat.setDouble(4, maxLat);
        ResultSet rs = stat.executeQuery();
        while (rs.next()) {
            count = rs.getInt("counter");
        }
        rs.close();
        stat.close();
        return count;
    }

    private static void generateRandomCrombs(Connection con, double lon, double lat) throws SQLException {
        PreparedStatement stat = con.prepareStatement(SQL_PUT_CROMB);
        Random rand = new Random(System.currentTimeMillis());
        String message = "Share http://crombz.com with your friends!";
        message += "<BR>Follow at http://twitter.com/crombmaster";
        stat.setString(1, "CrombMaster");
        stat.setString(2, message.replaceAll("'", "''"));
        stat.setDouble(3, lon);
        stat.setDouble(4, lat);
        stat.setInt(5, -999);
        stat.addBatch();
        for (int i = 0; i < 100; i++) {
            stat.setString(1, "CrombMaster");
            stat.setString(2, message.replaceAll("'", "''"));
            stat.setDouble(3, Math.round(lon * 10.0) / 10.0 + (rand.nextDouble() / 10));
            stat.setDouble(4, Math.round(lat * 10.0) / 10.0 + (rand.nextDouble() / 10));
            stat.setInt(5, -999);
            stat.addBatch();
            if (i % 100 == 0) {
                stat.execute();
            }
        }
        stat.executeBatch();
        stat.close();
    }

    public static ArrayList<Cromb> getCrombs(Connection con, double lon, double lat) throws SQLException {
        ArrayList<Cromb> list = new ArrayList();
        double minLon = lon -1;
        double minLat = lat -1;
        double maxLon = lon +1;
        double maxLat = lat +1;

        PreparedStatement stat = con.prepareStatement(SQL_GET_CROMBS);
        stat.setDouble(1, minLon);
        stat.setDouble(2, maxLon);
        stat.setDouble(3, minLat);
        stat.setDouble(4, maxLat);
        ResultSet rs = stat.executeQuery();
        while (rs.next()) {
            list.add(new Cromb(rs, lon, lat));
        }
        rs.close();
        stat.close();
        return list;
    }

    public static ArrayList<HashTags> getTagList(Connection con, double lon, double lat) throws SQLException {
        ArrayList<HashTags> list = new ArrayList();
        double minLon = lon - 1;
        double minLat = lat - 1;
        double maxLon = lon + 1;
        double maxLat = lat + 1;

        PreparedStatement stat = con.prepareStatement(SQL_GET_TAG_LIST);
        stat.setDouble(1, minLon);
        stat.setDouble(2, maxLon);
        stat.setDouble(3, minLat);
        stat.setDouble(4, maxLat);
        ResultSet rs = stat.executeQuery();
        while (rs.next()) {
            list.add(new HashTags(rs, lon, lat));
        }
        rs.close();
        stat.close();
        return list;
    }

    public static ArrayList<HashTags> getTagLocations(Connection con, double lon, double lat, String tag) throws SQLException {
        ArrayList<HashTags> list = new ArrayList();
        double minLon = lon - 1;
        double minLat = lat - 1;
        double maxLon = lon + 1;
        double maxLat = lat + 1;

        PreparedStatement stat = con.prepareStatement(SQL_GET_TAG_LOCATIONS);
        stat.setDouble(1, minLon);
        stat.setDouble(2, maxLon);
        stat.setDouble(3, minLat);
        stat.setDouble(4, maxLat);
        stat.setString(5, tag);
        ResultSet rs = stat.executeQuery();
        while (rs.next()) {
            list.add(new HashTags(rs, lon, lat));
        }
        rs.close();
        stat.close();
        return list;
    }

    public static CrombClosest getClosestCromb(Connection con, double lon, double lat) throws SQLException {
        double dist = 0;
        double minLon = lon - 1;
        double minLat = lat - 1;
        double maxLon = lon + 1;
        double maxLat = lat + 1;

        PreparedStatement stat = con.prepareStatement(SQL_GET_CLOSEST);
        stat.setDouble(1, lon);
        stat.setDouble(2, lat);
        stat.setDouble(3, minLon);
        stat.setDouble(4, maxLon);
        stat.setDouble(5, minLat);
        stat.setDouble(6, maxLat);
        ResultSet rs = stat.executeQuery();

        double dbLon = 0;
        double dbLat = 0;
        boolean noResult = true;
        while (rs.next()) {
            dbLon = rs.getDouble("longitude");
            dbLat = rs.getDouble("latitude");
            dist = GeoTools.distance(lat, dbLat, lon, dbLon, 0, 0);
            noResult = false;
        }

        rs.close();
        stat.close();
        CrombClosest c = new CrombClosest();
        if (noResult) {
            generateRandomCrombs(con, lon, lat);
            c.distance = 100000;
            c.orientation = 0;
        } else {
            c.distance = dist;
            c.orientation = GeoTools.bearing(lat, lon, dbLat, dbLon);
        }
        return c;
    }

    public static void updateCrombLikeCounter(Connection con, int crombId) throws SQLException {
        String sql = "UPDATE cromb set likeCounter=likeCounter+1 WHERE id=?";
        PreparedStatement stat = con.prepareStatement(sql);
        stat.setInt(1, crombId);
        stat.execute();
        stat.close();
    }

    public static void putCromb(Connection con, double lon, double lat, String author, String message, File image, int userId) throws SQLException {
        if (image != null) {
            message = "<img class=\"crombimage\" src=\"images/users/" + image.getName() + "\">" + message;
        }
        if (message != null) {
            PreparedStatement stat = con.prepareStatement(SQL_PUT_CROMB);
            stat.setString(1, author);
            stat.setString(2, message.replaceAll("'", "''").replaceAll("\n", "<br>") + "");
            stat.setDouble(3, lon);
            stat.setDouble(4, lat);
            stat.setDouble(5, userId);
            stat.execute();
            stat.close();

            addHashTags(con, message, lon, lat);
        }
    }

    private static void addHashTags(Connection con, String message, double lon, double lat) throws SQLException {
        int start = 0;
        ArrayList<String> tags = new ArrayList();
        message = message.replaceAll("\n", " ").replaceAll("\r", " ");
        while (message.indexOf("#", start) != -1) {
            start = message.indexOf("#", start);
            int end = message.indexOf(" ", start);
            String tag = "";
            if (end == -1) {
                tag = message.substring(start);
                start = message.length() - 1;
            } else {
                tag = message.substring(start, end);
                start = end;
            }
            tags.add(tag);
        }
        if (!tags.isEmpty()) {
            PreparedStatement stat = con.prepareStatement(SQL_PUT_TAGS);
            for (String tag : tags) {
                stat.setString(1, tag);
                stat.setDouble(2, lon);
                stat.setDouble(3, lat);
                stat.addBatch();
            }
            stat.executeBatch();
            stat.close();
        }
    }

    public static Connection getConnection(String host, String db, String user, String pass) throws SQLException {
        String url = "jdbc:mysql://" + host + "/" + db;
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(CrombManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return DriverManager.getConnection(url, user, pass);
    }

    public static String getXMLDistance(Connection con, double lon, double lat) throws ParserConfigurationException, SQLException, TransformerConfigurationException, TransformerException {
        Document doc = getBaseDocument();
        Node root = doc.getElementsByTagName("crombz").item(0);
        Node distance = doc.createElement("distance");
        Node value = doc.createAttribute("value");
        Node orientation = doc.createAttribute("orientation");
        CrombClosest c = getClosestCromb(con, lon, lat);
        value.setNodeValue(c.distance + "");
        orientation.setNodeValue(c.orientation + "");
        distance.getAttributes().setNamedItem(value);
        distance.getAttributes().setNamedItem(orientation);
        root.appendChild(distance);
        return getXML(doc);
    }

    public static String getXMLCount(Connection con, double lon, double lat) throws ParserConfigurationException, TransformerConfigurationException, TransformerException, SQLException {
        Document doc = getBaseDocument();
        Node root = doc.getElementsByTagName("crombz").item(0);
        Node distance = doc.createElement("count");
        Node value = doc.createAttribute("value");
        value.setNodeValue(getCrombsCount(con, lon, lat) + "");
        distance.getAttributes().setNamedItem(value);
        root.appendChild(distance);
        return getXML(doc);
    }

    public static String getXMLCrombs(Connection con, double lon, double lat) throws ParserConfigurationException, TransformerConfigurationException, SQLException, TransformerException {
        Document doc = getBaseDocument();
        Node root = doc.getElementsByTagName("crombz").item(0);
        ArrayList<Cromb> list = getCrombs(con, lon, lat);
        System.out.println("Cromb List: " + list.size());
        Cromb[] sorted = new Cromb[list.size()];
        for (int i = 0; i < sorted.length; i++) {
            sorted[i] = list.get(i);
        }
        Arrays.sort(sorted);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        for (int i = 0; i< 99 && i < sorted.length;i++){
            Cromb c = sorted[i];
            Node cromb = doc.createElement("cromb");
            cromb.setTextContent(c.getMessage());
            Node author = doc.createAttribute("author");
            Node datetime = doc.createAttribute("datetime");
            Node distance = doc.createAttribute("distance");
            Node orientation = doc.createAttribute("orientation");
            Node likeCounter = doc.createAttribute("likecounter");
            Node id = doc.createAttribute("id");
            Node clon = doc.createAttribute("lon");
            Node clat = doc.createAttribute("lat");
            clon.setNodeValue(c.getLongitude()+"");
            clat.setNodeValue(c.getLatitude()+"");
            author.setNodeValue(c.getAuthor());
            datetime.setNodeValue(sdf.format(c.getEntryTime().getTime()));
            distance.setNodeValue(c.getDistance() + "");
            orientation.setNodeValue(c.getOrientation() + "");
            likeCounter.setNodeValue(c.getLikeCounter() + "");
            id.setNodeValue(c.getId() + "");
            cromb.getAttributes().setNamedItem(author);
            cromb.getAttributes().setNamedItem(datetime);
            cromb.getAttributes().setNamedItem(distance);
            cromb.getAttributes().setNamedItem(orientation);
            cromb.getAttributes().setNamedItem(likeCounter);
            cromb.getAttributes().setNamedItem(id);
            cromb.getAttributes().setNamedItem(clon);
            cromb.getAttributes().setNamedItem(clat);
            root.appendChild(cromb);
        }
        return getXML(doc);
    }

    public static String getXMLTagList(Connection con, double lon, double lat, String userTag) throws ParserConfigurationException, TransformerConfigurationException, SQLException, TransformerException {
        Document doc = getBaseDocument();
        Node root = doc.getElementsByTagName("crombz").item(0);
        ArrayList<HashTags> list;
        if (userTag != null && userTag.length() > 0) {
            list = getTagLocations(con, lon, lat, userTag);
        } else {
            list = getTagList(con, lon, lat);
        }

        HashTags[] sorted = new HashTags[list.size()];
        for (int i = 0; i < sorted.length; i++) {
            sorted[i] = list.get(i);
        }
        Arrays.sort(sorted);
        for (HashTags t : sorted) {
            Node tag = doc.createElement("hashtag");
            Node distance = doc.createAttribute("distance");
            Node orientation = doc.createAttribute("orientation");
            Node name = doc.createAttribute("name");
            name.setNodeValue(t.getTag());
            distance.setNodeValue(t.getDistance() + "");
            orientation.setNodeValue(t.getOrientation() + "");
            tag.getAttributes().setNamedItem(distance);
            tag.getAttributes().setNamedItem(orientation);
            tag.getAttributes().setNamedItem(name);
            root.appendChild(tag);
        }
        return getXML(doc);
    }

    private static Document getBaseDocument() throws ParserConfigurationException {
        Document doc = DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument();
        doc.appendChild(doc.createElement("crombz"));
        return doc;
    }

    private static String getXML(Document doc) throws TransformerConfigurationException, TransformerException {
        TransformerFactory tf = TransformerFactory.newInstance();
        Transformer transformer = tf.newTransformer();
        transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
        StringWriter writer = new StringWriter();
        transformer.transform(new DOMSource(doc), new StreamResult(writer));
        return writer.getBuffer().toString().replaceAll("\n|\r", "");
    }
}
